# 数据库初始化函数
from db.database_manager import DatabaseManager

def initialize_database(db_manager: DatabaseManager):
    """初始化数据库表结构"""
    conn = None
    cursor = None

    try:
        conn = db_manager.get_connection()
        cursor = conn.cursor()

        # 创建酒店表
        if db_manager.db_type == "mysql":
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS hotels (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    location VARCHAR(255),
                    price_tier ENUM('经济', '中等', '高档', '豪华') DEFAULT '中等',
                    rating DECIMAL(3,2) DEFAULT 0.0,
                    available_rooms INT DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)

            cursor.execute("""
                CREATE TABLE IF NOT EXISTS bookings (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    hotel_id INT NOT NULL,
                    guest_name VARCHAR(255) NOT NULL,
                    checkin_date DATE NOT NULL,
                    checkout_date DATE NOT NULL,
                    room_count INT DEFAULT 1,
                    status ENUM('confirmed', 'cancelled') DEFAULT 'confirmed',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (hotel_id) REFERENCES hotels(id)
                )
            """)
        else:
            # SQLite 实现
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS hotels (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    location TEXT,
                    price_tier TEXT CHECK(price_tier IN ('经济', '中等', '高档', '豪华')) DEFAULT '中等',
                    rating REAL DEFAULT 0.0,
                    available_rooms INTEGER DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)

            cursor.execute("""
                CREATE TABLE IF NOT EXISTS bookings (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    hotel_id INTEGER NOT NULL,
                    guest_name TEXT NOT NULL,
                    checkin_date TEXT NOT NULL,
                    checkout_date TEXT NOT NULL,
                    room_count INTEGER DEFAULT 1,
                    status TEXT CHECK(status IN ('confirmed', 'cancelled')) DEFAULT 'confirmed',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (hotel_id) REFERENCES hotels(id)
                )
            """)

        # 检查酒店表是否为空
        cursor.execute("SELECT COUNT(*) FROM hotels")
        result = cursor.fetchone()
        hotel_count = result[0] if result else 0

        if hotel_count == 0:
            sample_hotels = [
                # 国内豪华酒店
                ("四季酒店", "北京", "豪华", 4.8, 25),
                ("万达瑞华酒店", "上海", "豪华", 4.9, 20),
                ("半岛酒店", "香港", "豪华", 4.9, 15),
                ("文华东方", "澳门", "豪华", 4.7, 18),
                ("柏悦酒店", "深圳", "豪华", 4.6, 22),
                ("宝格丽酒店", "上海", "豪华", 4.9, 12),
                ("华尔道夫", "北京", "豪华", 4.8, 16),

                # 国内高档酒店
                ("希尔顿酒店", "上海", "高档", 4.5, 35),
                ("万豪酒店", "北京", "高档", 4.4, 40),
                ("香格里拉", "广州", "高档", 4.6, 30),
                ("凯悦酒店", "成都", "高档", 4.3, 38),
                ("洲际酒店", "重庆", "高档", 4.4, 32),
                ("皇冠假日", "杭州", "高档", 4.2, 45),
                ("威斯汀酒店", "南京", "高档", 4.3, 36),

                # 国内中等酒店
                ("桔子水晶", "杭州", "中等", 4.2, 60),
                ("全季酒店", "南京", "中等", 4.1, 75),
                ("亚朵酒店", "西安", "中等", 4.3, 55),
                ("和颐酒店", "苏州", "中等", 4.0, 65),
                ("麗枫酒店", "武汉", "中等", 4.1, 70),
                ("维也纳酒店", "郑州", "中等", 4.0, 80),
                ("星程酒店", "长沙", "中等", 3.9, 68),

                # 国内经济型酒店
                ("如家快捷", "广州", "经济", 3.8, 120),
                ("7天连锁", "杭州", "经济", 3.5, 150),
                ("汉庭酒店", "天津", "经济", 3.7, 130),
                ("格林豪泰", "郑州", "经济", 3.6, 140),
                ("速8酒店", "长沙", "经济", 3.4, 160),
                ("99旅馆", "合肥", "经济", 3.3, 180),
                ("海友酒店", "济南", "经济", 3.5, 145),

                # 国际豪华酒店
                ("Burj Al Arab", "迪拜", "豪华", 4.9, 12),
                ("The Ritz Carlton", "纽约", "豪华", 4.8, 18),
                ("Four Seasons", "巴黎", "豪华", 4.7, 16),
                ("Mandarin Oriental", "伦敦", "豪华", 4.8, 14),
                ("The Peninsula", "东京", "豪华", 4.9, 10),
                ("St. Regis", "新加坡", "豪华", 4.8, 15),
                ("Waldorf Astoria", "芝加哥", "豪华", 4.7, 20),

                # 国际高档酒店
                ("Hilton", "新加坡", "高档", 4.5, 45),
                ("Marriott", "曼谷", "高档", 4.4, 50),
                ("Hyatt", "悉尼", "高档", 4.6, 35),
                ("Sheraton", "首尔", "高档", 4.3, 42),
                ("Westin", "温哥华", "高档", 4.4, 38),
                ("Radisson Blu", "柏林", "高档", 4.3, 40),
                ("Pullman", "吉隆坡", "高档", 4.2, 48),

                # 国际中等酒店
                ("Holiday Inn", "柏林", "中等", 4.1, 80),
                ("Novotel", "罗马", "中等", 4.0, 85),
                ("Radisson", "莫斯科", "中等", 4.2, 70),
                ("Best Western", "马德里", "中等", 3.9, 95),
                ("Mercure", "阿姆斯特丹", "中等", 4.1, 75),
                ("Ibis Styles", "布鲁塞尔", "中等", 4.0, 88),
                ("Ramada", "华沙", "中等", 3.8, 92),

                # 国际经济型酒店
                ("Ibis", "布鲁塞尔", "经济", 3.7, 120),
                ("Premier Inn", "都柏林", "经济", 3.8, 110),
                ("Motel 6", "洛杉矶", "经济", 3.5, 130),
                ("Super 8", "芝加哥", "经济", 3.4, 140),
                ("Travelodge", "爱丁堡", "经济", 3.6, 125),
                ("Comfort Inn", "迈阿密", "经济", 3.5, 135),
                ("Econo Lodge", "休斯顿", "经济", 3.3, 150),

                # 特色主题酒店
                ("冰雪奇缘主题酒店", "哈尔滨", "高档", 4.7, 28),
                ("海滨度假村", "三亚", "豪华", 4.8, 20),
                ("森林树屋酒店", "丽江", "中等", 4.5, 15),
                ("古镇民宿", "乌镇", "经济", 4.3, 25),
                ("温泉度假酒店", "黄山", "高档", 4.6, 30),
                ("沙漠帐篷酒店", "敦煌", "中等", 4.4, 18),
                ("海底世界酒店", "珠海", "豪华", 4.8, 8),

                # 商务会议酒店
                ("国际会议中心酒店", "北京", "高档", 4.5, 60),
                ("博览中心酒店", "上海", "高档", 4.4, 55),
                ("金融区商务酒店", "深圳", "中等", 4.2, 75),
                ("高新区智选酒店", "杭州", "中等", 4.1, 85),
                ("CBD快捷酒店", "广州", "经济", 3.9, 100),

                # 机场附近酒店
                ("首都机场酒店", "北京", "中等", 4.0, 90),
                ("浦东机场酒店", "上海", "中等", 4.1, 80),
                ("白云机场酒店", "广州", "经济", 3.8, 110),
                ("双流机场酒店", "成都", "经济", 3.7, 95),
                ("宝安机场酒店", "深圳", "中等", 4.0, 70)
            ]

            # 批量插入酒店数据
            insert_query = "INSERT INTO hotels (name, location, price_tier, rating, available_rooms) VALUES (?, ?, ?, ?, ?)"
            for hotel in sample_hotels:
                if db_manager.db_type == "mysql":
                    cursor.execute(insert_query.replace('?', '%s'), hotel)
                else:
                    cursor.execute(insert_query, hotel)

            print(f"数据库初始化完成，共插入 {len(sample_hotels)} 家酒店数据")

            # 可选：插入一些示例预订数据
            try:
                sample_bookings = [
                    (1, "张三", "2024-01-15", "2024-01-18", 2, "confirmed"),
                    (3, "李四", "2024-01-20", "2024-01-22", 1, "confirmed"),
                    (10, "王五", "2024-01-25", "2024-01-28", 3, "confirmed"),
                    (25, "赵六", "2024-02-01", "2024-02-03", 1, "confirmed"),
                    (50, "钱七", "2024-02-10", "2024-02-15", 2, "confirmed")
                ]

                booking_query = "INSERT INTO bookings (hotel_id, guest_name, checkin_date, checkout_date, room_count, status) VALUES (?, ?, ?, ?, ?, ?)"
                for booking in sample_bookings:
                    if db_manager.db_type == "mysql":
                        cursor.execute(booking_query.replace('?', '%s'), booking)
                    else:
                        cursor.execute(booking_query, booking)

                print("已插入示例预订数据")

            except Exception as e:
                print(f"插入示例预订数据失败: {e}，但酒店数据已成功插入")

        else:
            print(f"数据库已存在，当前有 {hotel_count} 家酒店，跳过初始化")

        conn.commit()

    except Exception as e:
        print(f"数据库初始化失败: {e}")
        if conn:
            conn.rollback()
        raise
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()